var express = require('express');
var router = express.Router();
var {connection, customQuery} = require('../mysql/index')


/* GET users listing. */
router.get('/', function (req, res, next) {
    console.log(req.query)
    var limit = req.query.limit || 10;
    var page = req.query.page || 1;
    var query = req.query.query || "";


    const sql = `SELECT COUNT(*) AS count FROM user_list`;

    customQuery(sql).then(data=>{
        const count = data[0].count;
        var sql = `SELECT * FROM user_list`;
        console.log('query', query)
        if (query !== "") {
            sql += ` WHERE user_name LIKE '%${query}%' OR user_code LIKE '%${query}%' OR real_name LIKE '%${query}%' OR role_name LIKE '%${query}%'`;
        }

        sql += ` LIMIT ${limit} OFFSET ${(page - 1) * limit}`;

        connection.query(sql, function (err, result) {
            if (err) throw err;
            res.status(200).send({
                code: 200, list: result,total:count
            })
        });
    })
});

// 注册接口，需要提供用户名、密码、手机号、邮箱、昵称和角色信息
router.post('/add', function (req, res, next) {
    // 解构请求体中的参数
    const {role_name, user_code='', user_name, password, phone, email, qq, note='', role_id,real_name='',passcode} = req.body;
    const moment = require('moment');
    const create_time = moment().format('YYYY-MM-DD HH:mm:ss');
    // 构造查询语句
    const sql = `SELECT * FROM user_list WHERE user_name='${user_name}'`;
    const sql1 = `SELECT * FROM user_list WHERE role_name='${role_name}'`;
    Promise.all([customQuery(sql), customQuery(sql1)]).then(results => {
        if (results[0].length !== 0) {
            return res.status(200).send({code: 301, msg: '账号已存在'});
        }
        if (results[1].length !== 0) {
            return res.status(200).send({code: 301, msg: '用户名已存在'});
        }
        const insertSql = `INSERT INTO user_list (role_name,user_code,user_name,password,phone,email,qq,note,role_id,create_time,passcode) VALUES ('${role_name}', '${user_code}', '${user_name}', '${password}', '${phone}', '${email}', '${qq}', '${note}', '${role_id}','${create_time}','${passcode}')`;
        customQuery(insertSql).then(results => {
            return res.status(200).send({code: 200, msg: '操作成功', list: results});
        })
    })
});
// 账号删除接口以post方式处理
router.post('/delete', function(req, res, next) {
    const { id } = req.body;
    console.log(id);
    if(id==1){
        return res.status(200).send({code:500,msg:'该用户无法删除'});
    }
    // 执行删除操作
    const sql = `DELETE FROM user_list WHERE id=${id}`;
    connection.query(sql, function(error, results, fields) {
        if (error) {
            console.error(error);
            return res.status(200).send({code:500,msg:'服务器错误，请联系管理员'});
        }

        return res.status(200).send({
            code:200,
            msg:'删除成功'
        });
    });
});

router.post('/update', function(req, res, next) {
    console.log('update')
    const { id, phone, email, qq, note='', role_id, real_name } = req.body;

    const sql = `UPDATE user_list SET  phone='${phone}', email='${email}', qq='${qq}', note='${note}', role_id='${role_id}', real_name='${real_name}' WHERE id=${id}`;

    customQuery(sql).then(results => {
        // After updating, fetch the updated data
        const fetchSql = `SELECT * FROM user_list WHERE id=${id}`;
        customQuery(fetchSql).then(updatedData => {
            return res.status(200).send({ code: 200, msg: '操作成功', userInfo: updatedData[0] });
        });
    });
});

router.post('/pass', function(req, res, next) {
    console.log('req.body',req.body)
    const { id, passtopic } = req.body;
    // First, fetch the existing passtopic data for the user
    const fetchSql = `SELECT passtopic FROM user_list WHERE id=${id}`;
    customQuery(fetchSql).then(existingData => {
        let existingPasstopic = JSON.parse(existingData[0].passtopic || '[]');
        // Merge and deduplicate the existing and new passtopic arrays
        let mergedPasstopic = [...new Set([...existingPasstopic, ...passtopic])];
        // Update the user's passtopic data
        const passJson = JSON.stringify(mergedPasstopic);
        const sql = `UPDATE user_list SET passtopic='${passJson}' WHERE id=${id}`;
        customQuery(sql).then(results => {
            // After updating, fetch the updated data
            customQuery(fetchSql).then(updatedData => {
                return res.status(200).send({ code: 200, msg: '操作成功', list: updatedData });
            });
        });
    });
});

router.post('/head', function(req, res, next) {
    const { id, head_photo } = req.body;
    console.log('进入头像接口', id, head_photo)
    const escaped_head_photo = head_photo.replace(/\\/g, '\\\\');
    const sql = `UPDATE user_list SET head_photo='${escaped_head_photo}' WHERE id=${id}`;
    customQuery(sql).then(results => {
        return res.status(200).send({ code: 200, msg: '操作成功' });
    });
});



module.exports = router;
